package com.mcppro.aidatabase.service;

import com.mcppro.aidatabase.dto.DatabaseOperationResult;
import com.mcppro.aidatabase.dto.IntentDto;
import com.mcppro.aidatabase.util.SafeSqlGenerator;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

/**
 * 安全的数据库操作服务
 * 
 * @author MCP Pro
 */
@Service
@Slf4j
public class DatabaseOperationService {

    private final JdbcTemplate jdbcTemplate;
    private final SafeSqlGenerator sqlGenerator;

    @Autowired
    public DatabaseOperationService(JdbcTemplate jdbcTemplate, SafeSqlGenerator sqlGenerator) {
        this.jdbcTemplate = jdbcTemplate;
        this.sqlGenerator = sqlGenerator;
    }

    /**
     * 执行数据库操作
     * 
     * @param intent 意图解析结果
     * @return 操作结果
     */
    public DatabaseOperationResult executeOperation(IntentDto.IntentResult intent) {
        long startTime = System.currentTimeMillis();
        
        try {
            log.debug("执行数据库操作 - 操作类型: {}, 表名: {}", intent.getOperation(), intent.getTableName());
            
            DatabaseOperationResult result = switch (intent.getOperation()) {
                case SELECT -> executeSelect(intent);
                case INSERT -> executeInsert(intent);
                case UPDATE -> executeUpdate(intent);
                case DELETE -> executeDelete(intent);
                case COUNT -> executeCount(intent);
                default -> DatabaseOperationResult.failure(
                    intent.getOperation().toString(),
                    "",
                    "不支持的操作类型: " + intent.getOperation(),
                    System.currentTimeMillis() - startTime
                );
            };
            
            long executionTime = System.currentTimeMillis() - startTime;
            result.setExecutionTimeMs(executionTime);
            
            log.debug("数据库操作完成 - 成功: {}, 耗时: {}ms", result.isSuccess(), executionTime);
            return result;
            
        } catch (Exception e) {
            long executionTime = System.currentTimeMillis() - startTime;
            log.error("数据库操作失败", e);
            
            return DatabaseOperationResult.failure(
                intent.getOperation().toString(),
                "",
                "数据库操作异常: " + e.getMessage(),
                executionTime
            );
        }
    }

    /**
     * 执行SELECT查询
     */
    private DatabaseOperationResult executeSelect(IntentDto.IntentResult intent) {
        try {
            SafeSqlGenerator.SqlWithParams sqlWithParams = sqlGenerator.generateSelect(intent);
            String sql = sqlWithParams.getSql();
            Object[] params = sqlWithParams.getParamsArray();
            
            log.debug("执行SELECT查询: {}, 参数: {}", sql, List.of(params));
            
            List<Map<String, Object>> data = jdbcTemplate.queryForList(sql, params);
            
            return DatabaseOperationResult.success(
                "SELECT",
                sql,
                data,
                data.size(),
                0
            );
            
        } catch (DataAccessException e) {
            log.error("SELECT查询失败", e);
            return DatabaseOperationResult.failure(
                "SELECT",
                "",
                "查询失败: " + e.getMessage(),
                0
            );
        } catch (IllegalArgumentException e) {
            log.error("SELECT查询参数无效", e);
            return DatabaseOperationResult.failure(
                "SELECT",
                "",
                "查询参数无效: " + e.getMessage(),
                0
            );
        }
    }

    /**
     * 执行INSERT操作
     */
    @Transactional
    private DatabaseOperationResult executeInsert(IntentDto.IntentResult intent) {
        try {
            SafeSqlGenerator.SqlWithParams sqlWithParams = sqlGenerator.generateInsert(intent);
            String sql = sqlWithParams.getSql();
            Object[] params = sqlWithParams.getParamsArray();
            
            log.debug("执行INSERT操作: {}, 参数: {}", sql, List.of(params));
            
            int affectedRows = jdbcTemplate.update(sql, params);
            
            return DatabaseOperationResult.success(
                "INSERT",
                sql,
                null,
                affectedRows,
                0
            );
            
        } catch (DataAccessException e) {
            log.error("INSERT操作失败", e);
            return DatabaseOperationResult.failure(
                "INSERT",
                "",
                "插入失败: " + e.getMessage(),
                0
            );
        } catch (IllegalArgumentException e) {
            log.error("INSERT操作参数无效", e);
            return DatabaseOperationResult.failure(
                "INSERT",
                "",
                "插入参数无效: " + e.getMessage(),
                0
            );
        }
    }

    /**
     * 执行UPDATE操作
     */
    @Transactional
    private DatabaseOperationResult executeUpdate(IntentDto.IntentResult intent) {
        try {
            SafeSqlGenerator.SqlWithParams sqlWithParams = sqlGenerator.generateUpdate(intent);
            String sql = sqlWithParams.getSql();
            Object[] params = sqlWithParams.getParamsArray();
            
            log.debug("执行UPDATE操作: {}, 参数: {}", sql, List.of(params));
            
            int affectedRows = jdbcTemplate.update(sql, params);
            
            return DatabaseOperationResult.success(
                "UPDATE",
                sql,
                null,
                affectedRows,
                0
            );
            
        } catch (DataAccessException e) {
            log.error("UPDATE操作失败", e);
            return DatabaseOperationResult.failure(
                "UPDATE",
                "",
                "更新失败: " + e.getMessage(),
                0
            );
        } catch (IllegalArgumentException e) {
            log.error("UPDATE操作参数无效", e);
            return DatabaseOperationResult.failure(
                "UPDATE",
                "",
                "更新参数无效: " + e.getMessage(),
                0
            );
        }
    }

    /**
     * 执行DELETE操作
     */
    @Transactional
    private DatabaseOperationResult executeDelete(IntentDto.IntentResult intent) {
        try {
            SafeSqlGenerator.SqlWithParams sqlWithParams = sqlGenerator.generateDelete(intent);
            String sql = sqlWithParams.getSql();
            Object[] params = sqlWithParams.getParamsArray();
            
            log.debug("执行DELETE操作: {}, 参数: {}", sql, List.of(params));
            
            int affectedRows = jdbcTemplate.update(sql, params);
            
            return DatabaseOperationResult.success(
                "DELETE",
                sql,
                null,
                affectedRows,
                0
            );
            
        } catch (DataAccessException e) {
            log.error("DELETE操作失败", e);
            return DatabaseOperationResult.failure(
                "DELETE",
                "",
                "删除失败: " + e.getMessage(),
                0
            );
        } catch (IllegalArgumentException e) {
            log.error("DELETE操作参数无效", e);
            return DatabaseOperationResult.failure(
                "DELETE",
                "",
                "删除参数无效: " + e.getMessage(),
                0
            );
        }
    }

    /**
     * 执行COUNT查询
     */
    private DatabaseOperationResult executeCount(IntentDto.IntentResult intent) {
        try {
            SafeSqlGenerator.SqlWithParams sqlWithParams = sqlGenerator.generateCount(intent);
            String sql = sqlWithParams.getSql();
            Object[] params = sqlWithParams.getParamsArray();
            
            log.debug("执行COUNT查询: {}, 参数: {}", sql, List.of(params));
            
            List<Map<String, Object>> data = jdbcTemplate.queryForList(sql, params);
            
            return DatabaseOperationResult.success(
                "COUNT",
                sql,
                data,
                data.size(),
                0
            );
            
        } catch (DataAccessException e) {
            log.error("COUNT查询失败", e);
            return DatabaseOperationResult.failure(
                "COUNT",
                "",
                "统计失败: " + e.getMessage(),
                0
            );
        } catch (IllegalArgumentException e) {
            log.error("COUNT查询参数无效", e);
            return DatabaseOperationResult.failure(
                "COUNT",
                "",
                "统计参数无效: " + e.getMessage(),
                0
            );
        }
    }

    /**
     * 测试数据库连接
     */
    public boolean testConnection() {
        try {
            jdbcTemplate.queryForObject("SELECT 1", Integer.class);
            return true;
        } catch (Exception e) {
            log.error("数据库连接测试失败", e);
            return false;
        }
    }

    /**
     * 获取表结构信息
     */
    public List<Map<String, Object>> getTableSchema(String tableName) {
        try {
            String sql = "DESCRIBE " + tableName;
            return jdbcTemplate.queryForList(sql);
        } catch (Exception e) {
            log.error("获取表结构失败: {}", tableName, e);
            return List.of();
        }
    }
}